DB Restore from Tier 2 to Cloud Hosted Environment – Kill Database Connection Issue

Fix for SQL Server import error when restoring BACPAC file to SSMS

Problem

Encountered an error while importing the Database (.bacpac) file into SQL Server Management Studio (SSMS).

*** Error importing database: Could not import package.
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 4630, Level 16, State 1, Line 1
The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server.
Alternatively, use the server level 'ALTER ANY CONNECTION' permission.

Error SQL72045: Script execution error. The executed script:
GRANT KILL DATABASE CONNECTION TO [ms_db_configreader];
Error Screenshot

Solution

Step 1

Download the .bacpac file from LCS (Lifecycle Services).

Step 2

Create a copy of the BACPAC file and store it in a separate folder. Right-click the file and rename it from .bacpac to .zip.

Step 3

Extract the ZIP file and locate the model.xml file. Move it to another folder for editing.

Step 4

In model.xml, remove the <Element> entry for Grant.KillDatabaseConnection.Database from both “ms_db_configreader” and “ms_db_configwriter” sections.

Error Screenshot

Step 5

Save the modified model.xml file. Open Command Prompt as Administrator and run: 

SqlPackage.exe /a:import /sf:J:\Testbackup.bacpac /mfp:E:\model.xml \
/tsn:localhost /tdn:AxDB_20240515 /p:CommandTimeout=12000 /ttsc:True

/mfp refers to /ModelFilePath — provide the path of the edited model.xml file.

Step 6

The .bacpac file will now import successfully into SSMS without the permission error.